﻿using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using SurveySystem.Model;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MySQLSurveyRepository : ISurveyRepository
    {

        #region " Private utility methods "
        private static Survey SurveyFromDataReader(MySqlDataReader dr)
        {
            Survey s = new Survey();
            MySqlDateTime open, close;


            s.SurveyID = dr.GetInt32(0);
            if (!(dr.GetValue(1) is System.DBNull))
                s.Title = dr.GetString(1);
            if (!(dr.GetValue(2) is System.DBNull))
                s.Description = dr.GetString(2);
            if (!(dr.GetValue(3) is System.DBNull))
                s.MoreInfoUrl = dr.GetString(3);
            if (!(dr.GetValue(4) is System.DBNull))
                s.Owner = dr.GetString(4);

            s.OpenDate = DateTime.MinValue;
            if (!(dr.GetValue(5) is System.DBNull))
            {
                try
                {
                    open = dr.GetMySqlDateTime(5);
                    s.OpenDate = open.Value;
                }
                catch (MySqlConversionException ex)
                {
                    // rather inelegant way of handling null or out of range values
                }
            }

            s.CloseDate = DateTime.MinValue;
            s.NoCloseDate = true;

            if (!(dr.GetValue(6) is System.DBNull))
            {
                try
                {
                    close = dr.GetMySqlDateTime(6);
                    s.CloseDate = close.Value;
                    s.NoCloseDate = false;
                }
                catch (MySqlConversionException ex)
                {
                    // rather inelegant way of handling null or out of range values
                }
            }

            if (!(dr.GetValue(7) is System.DBNull))
                s.Closed = dr.GetBoolean(7);
            return s;
        }
        #endregion

        public Survey GetSurveyByID(int surveyID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            Survey s = null;

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey` WHERE `survey_id`=?id";

            conn = new MySqlConnection(Globals.ConnectionString);
            
            try
            {
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    s = SurveyFromDataReader(dr);

                }
                else
                {
                    s = null;
                }

            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
            finally
            {
                try
                {
                    conn.Close();
                }   
                catch (MySqlException)
                {
                    // TODO: logging
                    throw;
                }
            }

            return s;
        }

        public List<Survey> GetAllActiveSurveys()
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<Survey> list = new List<Survey>();

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey` \n" + //
                  "WHERE (`close_date` > now() OR `close_date` IS NULL) AND `closed`=0";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Survey s = SurveyFromDataReader(dr);

                    list.Add(s);
                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<Survey> GetAllSurveys()
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<Survey> list = new List<Survey>();

            sql = "SELECT `survey_id`, `title`, `description`, `more_info_url`, `owner`, `open_date`, `close_date`, `closed` \n" + //
                  "FROM `survey`";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Survey s = SurveyFromDataReader(dr);

                    list.Add(s);
                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
            catch (Exception ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddSurvey(Survey s)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            sql = "INSERT INTO  `survey` \n" + //
                  "(`title`,`description`,`more_info_url`,`owner`,`close_date`,`closed`) \n" + //
                  "VALUES \n" + //
                  "( ?title, ?description, ?more_info_url, ?owner, ?close_date, ?closed)";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?title", s.Title);
                cmd.Parameters.AddWithValue("?description", s.Description);
                cmd.Parameters.AddWithValue("?more_info_url", s.MoreInfoUrl);
                cmd.Parameters.AddWithValue("?owner", s.Owner);
                cmd.Parameters.AddWithValue("?closed", s.Closed);

                // close date
                if (!s.NoCloseDate)
                    cmd.Parameters.AddWithValue("?close_date", s.CloseDate);
                else
                    cmd.Parameters.AddWithValue("?close_date", null);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();


                //retrieve the new ID field from the database
                sql = "SELECT @@IDENTITY";
                cmd = new MySqlCommand(sql, conn);
                dr = cmd.ExecuteReader();

                // update the Response object
                if (dr.Read())
                {
                    int newid = dr.GetInt32(0);

                    s.SurveyID = newid;

                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void UpdateSurvey(Survey s)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "UPDATE `survey` SET \n" + //
                  "          `title` = ?title, \n" + //
                  "    `description` = ?description, \n" + //
                  "  `more_info_url` = ?more_info_url, \n" + //
                  "          `owner` = ?owner, \n" + //
                  "     `close_date` = ?close_date, \n" + //
                  "         `closed` = ?closed \n" + //
                  "WHERE `survey_id` = ?survey_id \n";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?survey_id", s.SurveyID);
                cmd.Parameters.AddWithValue("?title", s.Title);
                cmd.Parameters.AddWithValue("?description", s.Description);
                cmd.Parameters.AddWithValue("?more_info_url", s.MoreInfoUrl);
                cmd.Parameters.AddWithValue("?owner", s.Owner);
                if (!s.NoCloseDate)
                    cmd.Parameters.AddWithValue("?close_date", s.CloseDate);
                else
                    cmd.Parameters.AddWithValue("?close_date", null);
                cmd.Parameters.AddWithValue("?closed", s.Closed);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void DeleteSurvey(int surveyID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "DELETE FROM `survey` \n" + //
                  " WHERE `survey_id` = ?survey_id";

            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?survey_id", surveyID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

    }
}
